package dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import dto.LanguageDTO;

import org.apache.log4j.Logger;

import util.Controller;

public class LanguagesDAO {
    public static Logger LOG = Logger.getLogger("db.DbLanguages");

    // add new language
    public boolean addLanguage(String name) {
        name = name.trim();
        if (name == null) {
            return false;
        }
        if (name == "") {
            return false;
        }
        if (name.length() == 0) {
            return false;
        }
        boolean bOK = false;
        Connection cnn = null;
        PreparedStatement pstatCheck = null;
        CallableStatement cstatInsert = null;
        ResultSet rs = null;
        try {
            cnn = Controller.getConnection();
            pstatCheck = cnn.prepareStatement("select * from m_languages where m_name='" + name + "'");
            rs = pstatCheck.executeQuery();
            if (rs.next() == false) {
                pstatCheck.close();
                cstatInsert = cnn.prepareCall("{call m_languages_insert(?)}");
                cstatInsert.setString(1, name);                        
                bOK = (cstatInsert.executeUpdate() == 1);
            }
        } catch (Exception ex) {
//            LOG.error("addLanguage", ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstatCheck != null) {
                    pstatCheck.close();
                }
                if (cstatInsert != null) {
                    cstatInsert.close();
                }
                if (cnn != null) {
                    cnn.close();
                }
            } catch (Exception exx) {
            }
        }
        return bOK;
    }

    // remove language by its name
    public boolean removeLanguage(String name) {
        Connection cnn = null;
        PreparedStatement pstatsel = null;
        ResultSet rs = null;
        int langID = -1;
        try {
            cnn = Controller.getConnection();
            pstatsel = cnn.prepareStatement("select m_id from m_languages where m_name=N'" + name + "'");
            rs = pstatsel.executeQuery();
            if (rs.next()) {
                langID = rs.getInt("m_id");
            }
            rs.close();
            pstatsel.close();
        } catch (Exception ex) {
//            LOG.error("removeLanguage(String)", ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstatsel != null) {
                    pstatsel.close();
                }
                if (cnn != null) {
                    cnn.close();
                }
            } catch (Exception exx) {
            }
        }
        if (langID != -1) {
            return this.removeLanguage(langID);
        } else {
            return false;
        }
    }

    // remove language by its ID
    public boolean removeLanguage(int langID) {
        boolean bOK = false;
        Connection cnn = null;
        PreparedStatement pstat = null;
        ResultSet rs = null;
        DictionariesDAO dictList = new DictionariesDAO();
        try {
            cnn = Controller.getConnection();
            pstat = cnn.prepareStatement("select m_id from m_dictionaries where m_fromID=" + langID + " or m_toID="
                    + langID + " group by m_id");
            rs = pstat.executeQuery();
            
            while (rs.next()) {
                dictList.deleteDict(rs.getInt("m_id"));
            }
            rs.close();
            pstat.close();
            pstat = cnn.prepareStatement("delete from m_languages where m_id=" + langID);
            bOK = (pstat.executeUpdate() == 1);
        } catch (Exception ex) {
//            LOG.error("removeLanguage(int)", ex);
        } finally {
            try {
                if (pstat != null) {
                    pstat.close();
                }
                if (cnn != null) {
                    cnn.close();
                }
            } catch (Exception exx) {
            }
        }
        return bOK;
    }

    public List<LanguageDTO> getLangList() {
        List<LanguageDTO> list = new ArrayList<LanguageDTO>();
        Connection cnn = null;
        PreparedStatement pstat = null;
        ResultSet rs = null;
        try {
            cnn = Controller.getConnection();
            pstat = cnn.prepareStatement("select m_id,m_name from m_languages");
            rs = pstat.executeQuery();
            while (rs.next()) {
                LanguageDTO dt = new LanguageDTO();
                dt.setId(rs.getInt("m_id"));
                dt.setName(rs.getString("m_name"));
                list.add(dt);
            }
        } catch (Exception ex) {
//        	LOG.error("getLangList", ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstat != null) {
                    pstat.close();
                }
                if (cnn != null) {
                    cnn.close();
                }
            } catch (Exception ex) {
            }
        }
        return list;
    }   
    public List<LanguageDTO> getAvailableLanguage(int fromLangID) {
    	Connection cnn = null;
    	PreparedStatement pstat = null;
    	ResultSet rs = null;
    	List<LanguageDTO> list = new ArrayList<LanguageDTO>();
    	try {
			cnn = Controller.getConnection();
			pstat = cnn.prepareStatement("select * from m_languages where m_id in (select m_toID from m_dictionaries " +
																				"where m_fromID = ?)");
			pstat.setInt(1, fromLangID);
			rs = pstat.executeQuery();
			
			while (rs.next()) {
				LanguageDTO language = new LanguageDTO();
				language.setId(rs.getInt("m_id"));
				language.setName(rs.getString("m_name"));
				list.add(language);
			}
		} catch (Exception e) {
			// TODO: handle exception
		}
		
		finally {
			try {
				if(rs != null) {
					rs.close();
				}
				
				if(pstat != null) {
					pstat.close();
				}
				
				if(cnn != null) {
					cnn.close();
				}
			}
			catch (Exception e) {
				// TODO: handle exception
			}
		}
		return list;
    }
}
